﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace WinLogicUniversity.DBBroker
{
    public class ItemBroker
    {
       static LogicUniversityT2Entities entity = new LogicUniversityT2Entities();

        #region AutoGen

        public static string AutoGenItemID()
        {
            int intId = 0; string Iid;
            try
            {
                intId = Convert.ToInt32(entity.Items.Max(g => g.ItemID).Substring(1));
            }
            catch (NullReferenceException)
            {
                Iid = "I001";
                return Iid;
            }
            finally
            {
                int MaxIid = Convert.ToInt32(intId) + 1;
                Iid = string.Format("I{0:000}", MaxIid);
            }
            return Iid;
        }

        public static string AutoGenItemDetailID()
        {
            int intIdD = 0; string IidD;
            try
            {
                intIdD = Convert.ToInt32(entity.ItemDetails.Max(g => g.ItemDetailID).Substring(2));
            }
            catch (NullReferenceException)
            {
                IidD = "ID0001";
                return IidD;
            }
            finally
            {
                int MaxIidD = Convert.ToInt32(intIdD) + 1;
                IidD = string.Format("ID{0:0000}", MaxIidD);  
            }
            return IidD;
        }

        public static string AutoGenBinNo()
        {
            int binno = 0; string Binid;
            try
            {
                binno = Convert.ToInt32(entity.Items.Max(g => g.BinNo).Substring(1));
            }
            catch
            {
                Binid = "A0001";
                return Binid;
            }
            finally
            {
                int MaxBinID = Convert.ToInt32(binno) + 1;
                Binid = string.Format("A{0:0000}", MaxBinID);
            }
            return Binid;
        }

        #endregion 

        public static IQueryable getItemList()
        {
            LogicUniversityT2Entities entity = new LogicUniversityT2Entities();
            var itemlist = from item in entity.Items
                           join itemdetail in entity.ItemDetails on item.ItemID equals itemdetail.ItemID
                           join category in entity.Categories on item.CategoryID equals category.CategoryID
                           join supplier in entity.Suppliers on itemdetail.SupplierID equals supplier.SupplierID
                           select new
                           {
                               Item_No = item.ItemID,
                               Description = item.ItemDescription,
                               Price = itemdetail.Price,
                               Stock = item.StockBalance,
                               SupplierName = supplier.SupplierName,
                               CategoryName = category.CategoryName,
                               BinNo = item.BinNo,
                               UOM = item.UOM,
                               ReorderLevel = item.ReorderLevel,
                               ReorderQty = item.ReorderQuantity,
                               Quantity = item.StockBalance,
                               FSup = item.FirstSupplier,
                               SSup = item.SecondSupplier,
                               TSup = item.ThirdSupplier,
                           };
            return itemlist;
        }   

    }
}
